const connection = require("../app/database");
const { to } = require("../utils");

class MomentService {
    async create(content, userId) {
        const statement = "INSERT INTO `moment` (content, user_id) values (?,?)";
        const [error, resp] = await to(connection.execute(statement, [content, userId]));
        if (error) {
            return Promise.reject(error);
        }
        const [result] = resp;
        return result;
    }

    // 给定默认值防止没有query
    async list(size = 10, offset = 0) {
        const statement = `SELECT m.id id,m.content content,m.createAt createTime,m.updateAt updateTime,
        JSON_OBJECT('id',u.id,'name',u.name,'avatarUrl',u.avatar_url,'createTime',u.createAt,'updateTime',u.updateAt) AS user,
        (SELECT COUNT(*) FROM comment c WHERE c.moment_id = m.id) AS commentCount,
        (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) AS labelCount
        FROM moment m
        LEFT JOIN user u ON m.user_id = u.id 
        LIMIT ? OFFSET ?`;
        // 预处理语句不支持数字类型，最好转化为字符串
        const [error, resp] = await to(connection.execute(statement, [String(size), String(offset)]));
        if (error) {
            return Promise.reject(error);
        }
        const [result] = resp;
        return result;
    }

    async detail(id) {
        const statement = `SELECT m.id id,m.content content,m.createAt createTime,m.updateAt updateTime,
        JSON_OBJECT('id',u.id,'name',u.name,'avatarUrl',u.avatar_url,'createTime',u.createAt,'updateTime',u.updateAt) AS user,
        
        (
            SELECT 
                JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id',c.id,'content',c.content,'commentId',c.comment_id,
                        'createdUser',JSON_OBJECT('id',cu.id,'name',cu.name,'avatarUrl',cu.avatar_url)
                    )
                )
            FROM comment c 
            LEFT JOIN user cu ON cu.id = c.user_id
            WHERE c.moment_id = m.id
        ) AS commentList,
        
        (
            JSON_ARRAYAGG(
                JSON_OBJECT(
                    'id',l.id,'name',l.name,
                    'createdUser',JSON_OBJECT('id',lu.id,'name',lu.name,'avatarUrl',lu.avatar_url)
                )
            )
        ) AS labelList
        FROM moment m
        LEFT JOIN user u ON m.user_id = u.id
        
        LEFT JOIN moment_label ml ON ml.moment_id = m.id
        LEFT JOIN label l ON ml.label_id = l.id
        LEFT JOIN user lu ON lu.id = l.user_id
        WHERE m.id = ?
        GROUP BY m.id`;
        const [error, resp] = await to(connection.execute(statement, [id]));
        if (error) {
            return Promise.reject(error);
        }
        const [result] = resp;
        return result;
    }

    async update(content, id) {
        const statement = `UPDATE  moment SET content = ? WHERE id = ?`;
        const [error, resp] = await to(connection.execute(statement, [content, id]));
        if (error) {
            return Promise.reject(error);
        }
        const [result] = resp;
        return result;
    }

    async remove(id) {
        const statement = `DELETE FROM moment WHERE id = ?`;
        const [error, resp] = await to(connection.execute(statement, [id]));
        if (error) {
            return Promise.reject(error);
        }
        const [result] = resp;
        return result;
    }

    async hasLabelId(momentId, labelId) {
        const statement = `SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?`;
        const [error, resp] = await to(connection.execute(statement, [momentId, labelId]));
        if (error) {
            return Promise.reject(error);
        }
        const [result] = resp;
        return !!result.length;
    }

    async addLabels(momentId, labelId) {
        const statement = `INSERT INTO moment_label (moment_id,label_id) values (?,?)`;
        const [error, resp] = await to(connection.execute(statement, [momentId, labelId]));
        if (error) {
            return Promise.reject(error);
        }
        const [result] = resp;
        return result;
    }
}

module.exports = new MomentService();
